﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace DatabaseInterface
{
    public partial class ConfigureConnection : Form
    {
        private Connection cnx;
        private XMLData.XMLData xmlData;
        public ConfigureConnection()
        {
            InitializeComponent();
            //this.xmlData = new XMLData.XMLData();
        }

        private void ConfigureConnection_Load(object sender, EventArgs e)
        {
            //LoadSQLInstances();
            //SqlConnection cnx = new SqlConnection(Properties.Settings.Default.CarbonTrackerConnectionString);
            try
            {
                this.cnx = new Connection();
                cmbSqlServers.Text = cnx.DataSource;
                txtPassword.Text = cnx.Password;
                txtUsername.Text = cnx.User;
                cmdDatabases.Text = cnx.Catalog;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
                //cmbSqlServers.Text = this.xmlData.GetDato("Connection", "DataSource");
            //txtPassword.Text = this.xmlData.GetDato("Connection", "Password");
            //txtUsername.Text = this.xmlData.GetDato("Connection", "UserID");
            //cmdDatabases.Text = this.xmlData.GetDato("Connection", "InitialCatalog");

        }
        private void LoadSQLInstances()
        {
            //this.sqlExpressCombo1.Refresh_List();
            cmbSqlServers.Items.Clear();
            System.Data.Sql.SqlDataSourceEnumerator en = System.Data.Sql.SqlDataSourceEnumerator.Instance;
            DataTable t = en.GetDataSources();
            foreach (DataRow r in t.Rows)
            {
                cmbSqlServers.Items.Add(r[0].ToString() + @"\" + r[1].ToString());
            }
        }
        
        private void cmdDatabases_DropDown(object sender, EventArgs e)
        {
            try
            {
                System.Data.SqlClient.SqlConnection cnx = new System.Data.SqlClient.SqlConnection();
                cnx.ConnectionString = "Data Source=" + cmbSqlServers.Text + ";User ID=" + txtUsername.Text + ";Password=" + txtPassword.Text;
                cnx.Open();
                DataTable catalogTable = cnx.GetSchema("Databases");
                cnx.Close();

                foreach (DataRow row in catalogTable.Rows)
                {
                    cmdDatabases.Items.Add(row[0].ToString());
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void sqlExpressCombo1_DropDown(object sender, EventArgs e)
        {
            
        }

        private void button1_Click(object sender, EventArgs e)
        {
            //Data Source=HP-FEDE\SQLEXPRESS;Initial Catalog=CarbonTracker;Persist Security Info=True;User ID=sa;Password=sasasa
            
            //String connection = "Data Source=" + cmbSqlServers.Text +";";
            //connection += "Initial Catalog=" + cmdDatabases.Text + ";";
            //connection += "Persist Security Info=True;";
            //connection += "User ID=" + txtUsername.Text + ";";
            //connection += "Password=" + txtPassword.Text;

            //Properties.Settings.Default.CarbonTrackerConnectionString = connection;
            //Properties.Settings.Default.Save();

            //MessageBox.Show(Properties.Settings.Default.CarbonTrackerConnectionString);
            //this.xmlData.SetDato("Connection", "DataSource", cmbSqlServers.Text);
            //this.xmlData.SetDato("Connection", "UserID", txtUsername.Text);
            //this.xmlData.SetDato("Connection", "Password", txtPassword.Text);
            //this.xmlData.SetDato("Connection", "InitialCatalog", cmdDatabases.Text);
            //this.xmlData.SetDato("Connection", "DataSource", cmbSqlServers.Text);
            this.cnx.User = txtUsername.Text;
            this.cnx.Password = txtPassword.Text;
            this.cnx.Catalog = cmdDatabases.Text;
            this.cnx.DataSource = cmbSqlServers.Text;
            this.cnx.Save();

            this.Close();
        }

        private void btnTest_Click(object sender, EventArgs e)
        {
            SqlConnectionStringBuilder cnxBuilder = new SqlConnectionStringBuilder();
            cnxBuilder.UserID = txtUsername.Text;
            cnxBuilder.Password = txtPassword.Text;
            cnxBuilder.InitialCatalog = cmdDatabases.Text;
            cnxBuilder.DataSource = cmbSqlServers.Text;
            SqlConnection connection = new SqlConnection(cnxBuilder.ConnectionString);
            SqlCommand cmd = new SqlCommand("SELECT getdate()", connection);
            SqlDataReader dr;
            try
            {
                connection.Open();
                dr = cmd.ExecuteReader();
                if (!dr.HasRows)
                {
                    throw new Exception();
                }
                else
                {
                    MessageBox.Show("Connection Successful", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                connection.Close();
                
            }
            catch(Exception ex)
            {
                MessageBox.Show("SQL Server Connection Failed\r" + ex.Message, "Connection Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                if (connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }
        }

        private void cmbSqlServers_SelectedIndexChanged(object sender, EventArgs e)
        {
            
        }

        private void cmbSqlServers_DropDown(object sender, EventArgs e)
        {
            LoadSQLInstances();
        }
    }
}
